﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Xml;
using BEGDC.Utility.NHibernate;
using Castle.Facilities.NHibernateIntegration;
using Castle.Services.Transaction;
using NHibernate;

namespace BEGDC.PreContract.BLL
{
    public partial class SalesFeedback : ISalesFeedback
    {
        public const string SQL_SELECT_ALLFEEDBACKCONTRACT = @"select * from vw_FeedbackPreContract ";

        public const string SQL_SELECT_MYFEEDBACKCONTRACT = @"select * from vw_FeedbackPreContract v left join ProvinceAssignment p on p.ProvinceID = v.ProvinceID left join CATAssignment c on c.CATID = v.CATID left join BDAssignment b on b.BDID = v.BDID where p.EmployeeID = c.EmployeeID and c.EmployeeID = b.EmployeeID ";

        public const string SQL_SELECT_ALLNOFEEDBACKCONTRACT = @"select * from vw_NoFeedbackPreContract ";

        public const string SQL_SELECT_MYNOFEEDBACKCONTRACT = @"select * from vw_NoFeedbackPreContract v left join ProvinceAssignment p on p.ProvinceID = v.ProvinceID left join CATAssignment c on c.CATID = v.CATID left join BDAssignment b on b.BDID = v.BDID where p.EmployeeID = c.EmployeeID and c.EmployeeID = b.EmployeeID ";

        public const string SQL_SELECT_VWFEEDBACKINFO = @"select * from vw_FeedbackInfo ";

        /// <summary>
        ///  PreContracts Which have Feedbacks
        /// </summary>
        /// <param name="employeeID">current user</param>
        /// <param name="isAdmin">admin</param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public DataTable GetFeedbackPreContract(string tbContractNo, string tbContractName, string tbProduct, int employeeID, bool isAdmin)
        {
            using (ISession session = factory.OpenSession())
            {
                string strWhere = string.Empty;

                if (!string.IsNullOrEmpty(tbContractNo))
                {
                    strWhere += string.Format(" and PreContractNo like '%{0}%'", tbContractNo);
                }

                if (!string.IsNullOrEmpty(tbContractName))
                {
                    strWhere += string.Format(" and ContractName like N'%{0}%'", tbContractName);
                }

                if (!string.IsNullOrEmpty(tbProduct))
                {
                    strWhere += string.Format(" and ProductName like N'%{0}%'", tbProduct);
                }

                string strSql = string.Empty;

                if (isAdmin)
                {
                    strSql = SQL_SELECT_ALLFEEDBACKCONTRACT;

                    if (!string.IsNullOrEmpty(strWhere))
                    {
                        strSql += " Where 1=1" + strWhere;
                    }
                    strSql += " order by OperationDate desc";
                }
                else
                {
                    strSql = SQL_SELECT_MYFEEDBACKCONTRACT;

                    if (!string.IsNullOrEmpty(strWhere))
                    {
                        strSql += strWhere;
                    }

                    strSql += string.Format(" and p.EmployeeID = {0} order by v.OperationDate desc", employeeID);
                }

                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);
                return dtSearch;
            }
        }

        /// <summary>
        /// PreContracts Which have no Feedbacks
        /// </summary>
        /// <param name="employeeID">current user</param>
        /// <param name="isAdmin">admin</param>
        /// <returns></returns>
        [Transaction(TransactionMode.Requires)]
        public DataTable GetNoFeedbackPreContract(string tbContractNo, string tbContractName, string tbProduct, int employeeID, bool isAdmin)
        {
            using (ISession session = factory.OpenSession())
            {
                string strWhere = string.Empty;

                if (!string.IsNullOrEmpty(tbContractNo))
                {
                    strWhere += string.Format(" and PreContractNo like '%{0}%'", tbContractNo);
                }

                if (!string.IsNullOrEmpty(tbContractName))
                {
                    strWhere += string.Format(" and ContractName like N'%{0}%'", tbContractName);
                }

                if (!string.IsNullOrEmpty(tbProduct))
                {
                    strWhere += string.Format(" and ProductName like N'%{0}%'", tbProduct);
                }

                string strSql = string.Empty;

                if (isAdmin)
                {
                    strSql = SQL_SELECT_ALLNOFEEDBACKCONTRACT;

                    if (!string.IsNullOrEmpty(strWhere))
                    {
                        strSql += " Where 1=1" + strWhere;
                    }
                    strSql += " order by PreContractID desc";
                }
                else
                {
                    strSql = SQL_SELECT_MYNOFEEDBACKCONTRACT;

                    if (!string.IsNullOrEmpty(strWhere))
                    {
                        strSql += strWhere;
                    }

                    strSql += string.Format(" and p.EmployeeID = {0} order by v.PreContractID desc", employeeID);
                }
                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);
                return dtSearch;
            }
        }

        /// <summary>
        /// The number of Pre-contracts which have a unexpired feedback
        /// </summary>
        [Transaction(TransactionMode.Requires)]
        public int GetFeedbackPreContractCount()
        {
            using (ISession session = factory.OpenSession())
            {
                DataSet ds = new DataSet();
                string strSql = SQL_SELECT_ALLFEEDBACKCONTRACT;
                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch != null ? dtSearch.Rows.Count : 0;
            }
        }

        /// <summary>
        /// The number of Pre-contracts which feedbacks are expired or not existed
        /// </summary>
        [Transaction(TransactionMode.Requires)]
        public int GetNoFeedbackPreContractCount()
        {
            using (ISession session = factory.OpenSession())
            {
                DataSet ds = new DataSet();
                string strSql = SQL_SELECT_ALLNOFEEDBACKCONTRACT;
                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);

                return dtSearch != null ? dtSearch.Rows.Count : 0;
            }
        }

        /// <summary>
        /// Query Feedbacks by its pre-contractID
        /// </summary>
        /// <param name="preContractID">PreContract ID</param>
        /// <returns>Feedback list</returns>
        [Transaction(TransactionMode.Requires)]
        public DataTable GetFeedbackByPreContractID(int preContractID)
        {
            using (ISession session = factory.OpenSession())
            {
                string strSql = SQL_SELECT_VWFEEDBACKINFO + string.Format(" where PreContractID = {0} order by CreateTime desc", preContractID);
                DataTable dtSearch = DAO.ExecuteDataTable(session, strSql);
                return dtSearch;
            }
        }
    }
}
